package com.system_jsp.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.system_jsp.bean.UserBean;
import com.system_jsp.util.MD5;

public class UserDao {

	/**
	 * 通过用户名检查用户是否存在，存在则返回用户实体类，不存在返回null
	 * 
	 * @param username
	 * @return UserinfoBean
	 */
	public UserBean login(String username) {
		String sql = "select username, passwd from userinfo where username = '"
				+ username + "';";
		ResultSet rs = DbManager.queryDb(sql);
		try {
			if (rs.next()) {
				UserBean ub = new UserBean();
				ub.setUsername(rs.getString("username"));
				ub.setPasswd(rs.getString("passwd"));
				return ub;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbManager.close();
		}
		return null;
	}

	/**
	 * 查询所有用户
	 * 
	 * @return List<UserinfoBean>
	 */
	public List<UserBean> search() {
		String sql = "select * from userinfo;";
		ResultSet rs = DbManager.queryDb(sql);
		List<UserBean> list = new ArrayList<UserBean>();
		try {
			while (rs.next()) {
				UserBean ub = new UserBean();
				ub.setUsername(rs.getString("username"));
				ub.setGender(rs.getString("gender"));
				ub.setBirth(rs.getString("birth"));
				ub.setPhone(rs.getString("phone"));
				ub.setAddress(rs.getString("address"));
				ub.setRegdate(new SimpleDateFormat("yyyy年MM月dd日 HH:mm:ss")
						.format(rs.getTimestamp("regdat")));
				list.add(ub);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbManager.close();
		}
		return list;
	}

	/**
	 * 查询分页用户
	 * 
	 * @return List<UserinfoBean>
	 */
	public List<UserBean> search(int pageSize, int pageNow) {
		String sql = "select * from userinfo limit " + (pageNow - 1) * pageSize + "," + pageSize
				+ ";";
		ResultSet rs = DbManager.queryDb(sql);
		List<UserBean> list = new ArrayList<UserBean>();
		try {
			while (rs.next()) {
				UserBean ub = new UserBean();
				ub.setUsername(rs.getString("username"));
				ub.setGender(rs.getString("gender"));
				ub.setBirth(rs.getString("birth"));
				ub.setPhone(rs.getString("phone"));
				ub.setAddress(rs.getString("address"));
				ub.setRegdate(new SimpleDateFormat("yyyy年MM月dd日 HH:mm:ss")
						.format(rs.getTimestamp("regdat")));
				list.add(ub);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbManager.close();
		}
		return list;
	}

	/**
	 * 添加用户
	 * 
	 * @param ub
	 * @return 影响行数
	 */
	public int addUser(UserBean ub) {
		String sql = "insert into userinfo values(null,'" + ub.getUsername()
				+ "', '" + MD5.md5Encode(ub.getPasswd()) + "', '" + ub.getGender() + "', '"
				+ ub.getBirth() + "', '" + ub.getPhone() + "', '"
				+ ub.getAddress() + "', now());";
		return DbManager.updateDb(sql);
	}

	/**
	 * 删除用户
	 * 
	 * @param username
	 * @return 影响行数
	 */
	public int deleteUser(String username) {
		String sql = "delete from userinfo where username = '" + username
				+ "';";
		return DbManager.updateDb(sql);
	}

	/**
	 * 更新用户信息
	 * 
	 * @param ub
	 * @return 影响行数
	 */
	public int updateuser(UserBean ub) {
		String sql = "update userinfo set passwd = '" + ub.getPasswd()
				+ "', gender = '" + ub.getGender() + "', birth = '"
				+ ub.getBirth() + "', phone = '" + ub.getPhone()
				+ "', address = '" + ub.getAddress() + "' where username = '"
				+ ub.getUsername() + "';";
		return DbManager.updateDb(sql);
	}

	public int totalRecord() {
		String sql = "select count(*) from userinfo";
		ResultSet rs = DbManager.queryDb(sql);
		int totalRecord = 0;
		try {
			rs.next();
			totalRecord = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbManager.close();
		}
		return totalRecord;
	}
}
